package com.wxtf.platform.main.keyserobj;

import java.io.File;
import java.util.List;

import javax.servlet.http.HttpServletRequest;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

import org.apache.commons.lang.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.wxtf.platform.interfaces.CommonsCallProvider;
import com.wxtf.platform.plugins.user.UserBean;
import com.wxtf.platform.supers.LogicBase;
import com.wxtf.platform.tools.Common;
import com.wxtf.platform.tools.ConstantInfoHelper;
import com.wxtf.platform.tools.AccessDataBaseUtil;
import com.wxtf.platform.tools.FormatUtil;
import com.wxtf.platform.tools.XLSUtils;

/**
 * <li>Title: KeyReg.java</li>
 * <li>Project: baseplatform</li>
 * <li>Package: com.wxtf.platform.main.keyserobj</li>
 * <li>Description: 重点服务对象访视登记</li>
 * <li>Copyright: Copyright (c) 2012</li>
 * <li>Company: wxthtf Technologies </li>
 * <li>Created on Aug 28, 2012 14:40:02 AM</li>
 * 
 * @author wangzhi
 * @version 1.0.0.0
 * 
 */
public class KeyReg extends LogicBase implements CommonsCallProvider{
	private static final Log log = LogFactory.getLog(KeyReg.class);

	public KeyReg(String jsonArray){

	}
	
	@Override
	public Object run() {
		Object msg = "";
		try {
			if("queryPage".equals(this.getMethodName())) {
				msg = this.select(this.getLogicData());
				return msg;
			} else if("load_detailPanel".equals(this.getMethodName())) {
				msg = this.load_detailPanel(this.getLogicData());
				return msg;
			} else if("insert".equals(this.getMethodName())) {
				msg = this.insert(this.getLogicData());
			} else if("yuqi".equals(this.getMethodName())) {
				msg = this.yuqi(this.getLogicData());
			} else if("xgfs".equals(this.getMethodName())) {
				msg = this.xgfs(this.getLogicData());
			} else if("load_and_set".equals(this.getMethodName())) {
				msg = this.load_and_set(this.getLogicData());
				return msg.toString();
			} else if("expData".equals(this.getMethodName())) {
				msg = this.expData(this.getLogicData());
			} else {
				msg = "\"没有【" + this.getMethodName() + "】该方法\"";
			}
			if(msg == null || "[null]".equals(msg)) {
				msg = "\"\"";
			}
			if(msg.toString().startsWith("{") || msg.toString().startsWith("[")){
				return msg;
			}
			msg = ConstantInfoHelper.generateJSONString(msg.toString());
		} catch(Exception e) {
			msg = ConstantInfoHelper.generateErrorJSONString();
			log.error(e, e);
		} finally {
			this.clear();
		}
		return msg.toString();
	}

	String[] fields = {
			"tbe_id","org_id","tbe_idcard","tbe_work_permit",
			"tbe_employment_forms","tbe_name","tbe_former_name","tbe_spell_code","tbe_sex","tbe_age","tbe_birthday","tbe_nation","tbe_native_palce",
			"tbe_birthplace","tbe_join_time","tbe_seniority","tbe_family_phone","tbe_mobile","tbe_address","tbe_marital_status","tbe_marital_condition",
			"tbe_married_time","tbe_marriage_certificate","tbe_political_status","tbe_qualification","tbe_graduate_college","tbe_major","tbe_work_area",
			"tbe_position","tbe_is_again_birth","tbe_is_barrenness","tbe_is_late_marriage","tbe_is_late_birth","tbe_status","tbe_ba_status",
			"tbe_birth_control_measures","tbe_es_time","tbe_is_es","tbe_es_reason","tbe_children_status","tbe_order","tbe_memo",
			"reserve1","reserve2","reserve3","reserve4","reserve5","create_by","create_time","last_modify_by","last_modify_time"
	};
	@Override
	public Object select(Object jsonArray) throws Exception {
		
		String[] fields_select={"reserve1","tbe_id","org_id","tbe_name","tbe_idcard","tesvr_time","tbe_sex","tbe_age","tbe_marital_status","tbfi_children_num",
				"tbe_spouse_id","tbfi_spouse_name","tbfi_spouse_sex","tbfi_spouse_age","tbfi_spouse_birthday","tbfi_spouse_idcard",
				"tbfi_spouse_position","tbfi_spouse_is_join","tbfi_children_num","tbfi_spouse_department",
				"tbe_birth_control_measures","tbe_es_reason","tbe_address"};
		UserBean user = (UserBean)this.getRequest().getSession().getAttribute("userBean");		
		sql = " from tb_employee t1 left join tb_family_base t2 on t1.tbe_id=t2.tbe_id left join tes_vistor_record t3 on t3.tbfi_id=t2.tbfi_id where t1.tbe_is_es='1' and  t1.tbe_employment_forms <> '31' and t1.tbe_status not in ('102-3','102-4','102-5')";	
		JSONArray array = JSONArray.fromObject(jsonArray);
		if(array.size() > 0) {
			JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
			String types = jsonObject.getString("types"); //类型
			if("init".equals(types)) {// 初始查询			
					sql = " from tb_employee t1 left join tb_family_base t2 on t1.tbe_id=t2.tbe_id left join tes_vistor_record t3 on t3.tbfi_id=t2.tbfi_id  where t1.tbe_is_es='1' and t1.tbe_employment_forms <> '31' and t1.tbe_status not in ('102-3','102-4','102-5') and t1.org_id  in (" + user.getCurSubOrgId() + ")  ";  
			} 
			else if("serch1".equals(types)) {// 条件查询
				String orgIds = jsonObject.getString("orgIds");//机构ID串
				String tbe_name = jsonObject.getString("tbe_name");//姓名
				String tbe_idcard = jsonObject.getString("tbe_idcard");//身份证	
				StringBuffer buff = new StringBuffer();
				if(StringUtils.isBlank(orgIds)){
					buff.append(" and t1.org_id in (").append(user.getCurSubOrgId()).append(")");
				} else {
					buff.append(" and t1.org_id in (").append(user.getCurSubOrgIds(orgIds)).append(")");
				}
				if(StringUtils.isNotBlank(tbe_name)){
					buff.append(" and t1.tbe_name like '%").append(tbe_name).append("%' ");
				}
				
				if(StringUtils.isNotBlank(tbe_idcard)){
					buff.append(" and t1.tbe_idcard like '%").append(tbe_idcard).append("%' ");
				}
				sql += buff.toString();
			} 
		}	
		String countSql = "select count(*) from( select count(*) " + sql+" group by t1.tbe_id order by t3.tesvr_time asc ) as ttt ";
		String querySql = "select t1.reserve1,t1.tbe_id,t1.org_id,t1.tbe_name,t1.tbe_idcard,MAX(t3.tesvr_time),t1.tbe_sex,t1.tbe_age,t1.tbe_marital_status,t2.tbfi_children_num,t2.tbe_spouse_id,t2.tbfi_spouse_name,t2.tbfi_spouse_sex,t2.tbfi_spouse_age,t2.tbfi_spouse_birthday,t2.tbfi_spouse_idcard,t2.tbfi_spouse_position,t2.tbfi_spouse_is_join,t2.tbfi_children_num,t2.tbfi_spouse_department,t1.tbe_birth_control_measures,t1.tbe_es_reason,t1.tbe_address " + sql +" group by t1.tbe_id order by t3.tesvr_time asc ";
		return AccessDataBaseUtil.query4SQLPagging(fields_select, countSql, querySql, this.getPage(), this.getPagesize(), null);
	}
	    
	    // 分割字符串-得到月份
	public String Split(String str) {
		String str_1 = "";// 定义字符串
		String[] array = new String[100];
		array = str_1.split("-");
		return array[1];
	}

	// 分割字符串-得到年份
	public String Split_year(String str) {
		String str_1 = "";// 定义字符串
		String[] array = new String[100];
		array = str_1.split("-");
		return array[0];
	} 
		
	// 登记随访记录
	@Override
	public Object insert(Object jsonArray) throws Exception {
		JSONArray array = JSONArray.fromObject(jsonArray);
		JSONObject jsonObject1 = JSONObject.fromObject(array.getString(0));
		String sql_s1="";
		String tbe_id = jsonObject1.getString("tbe_id");//员工ID	
		String tesvr_description = jsonObject1.getString("tesvr_description");//访视记录
		String tesvr_vistor = jsonObject1.getString("tesvr_vistor");//访视人
		String tesvr_time = jsonObject1.getString("tesvr_time");//访视时间
		String tesvr_memo = jsonObject1.getString("tesvr_memo");//备注	
		String tip="";
		if(!"".equals(tesvr_time)) {
			sql_s1 = ins_or_upd(tbe_id, tesvr_description, tesvr_vistor, tesvr_time, tesvr_memo);
			String[] sqls = { sql_s1 };
			int []row_num_1= AccessDataBaseUtil.executeSQLUpdate(sqls);
			if(row_num_1[0]>0)
			{tip=tip+"[第一季度] ";}
		}
		
        String sql_s2="";
		String tesvr_description_s2 = jsonObject1.getString("tesvr_description_s2");//访视记录
		String tesvr_vistor_s2 = jsonObject1.getString("tesvr_vistor_s2");//访视人
		String tesvr_time_s2 = jsonObject1.getString("tesvr_time_s2");//访视时间
		String tesvr_memo_s2 = jsonObject1.getString("tesvr_memo_s2");//备注
		if(!"".equals(tesvr_time_s2)) {
			sql_s2 = ins_or_upd(tbe_id, tesvr_description_s2, tesvr_vistor_s2, tesvr_time_s2, tesvr_memo_s2);
			String[] sqls = { sql_s2 };
			int []row_num_2=AccessDataBaseUtil.executeSQLUpdate(sqls);
			if(row_num_2[0]>0)
			{tip=tip+"[第二季度] ";}
		}
		
		String sql_s3="";
		String tesvr_description_s3 = jsonObject1.getString("tesvr_description_s3");//访视记录
		String tesvr_vistor_s3 = jsonObject1.getString("tesvr_vistor_s3");//访视人
		String tesvr_time_s3 = jsonObject1.getString("tesvr_time_s3");//访视时间
		String tesvr_memo_s3 = jsonObject1.getString("tesvr_memo_s3");//备注
		if(!"".equals(tesvr_time_s3)) {
			sql_s3 = ins_or_upd(tbe_id, tesvr_description_s3, tesvr_vistor_s3, tesvr_time_s3, tesvr_memo_s3);
			String[] sqls = { sql_s3 };
			int []row_num_3=AccessDataBaseUtil.executeSQLUpdate(sqls);
			if(row_num_3[0]>0)
			{tip=tip+"[第三季度] ";}
		}

		String sql_s4 = "";
		String tesvr_description_s4 = jsonObject1.getString("tesvr_description_s4");// 访视记录
		String tesvr_vistor_s4 = jsonObject1.getString("tesvr_vistor_s4");// 访视人
		String tesvr_time_s4 = jsonObject1.getString("tesvr_time_s4");// 访视时间
		String tesvr_memo_s4 = jsonObject1.getString("tesvr_memo_s4");// 备注
		if(!"".equals(tesvr_time_s4)) {
			sql_s4 = ins_or_upd(tbe_id, tesvr_description_s4, tesvr_vistor_s4, tesvr_time_s4, tesvr_memo_s4);
			String[] sqls = { sql_s4 };
			int []row_num_4=AccessDataBaseUtil.executeSQLUpdate(sqls);
			if(row_num_4[0]>0)
			{tip=tip+"[第四季度] ";}
		}
		if(tip!="")
		{return "\"已登记：" + tip + "\"";}
		else
		{return "\"未执行登记操作\"";}
	}
	
	
	//登记随访-insert 、 update 方法
	public String ins_or_upd(String tbe_id,String tesvr_description,String tesvr_vistor,String tesvr_time,String tesvr_memo) throws Exception
	{
		UserBean user = (UserBean)this.getRequest().getSession().getAttribute("userBean");
		String curTime = FormatUtil.getCurentDateTime();
		//判断第几季度
		String u_season="";
		String []a=tesvr_time.split("-");
		String u_month=a[1];
		if("01".equals(u_month)){u_season="第一季度";}
		if("02".equals(u_month)){u_season="第一季度";}
		if("03".equals(u_month)){u_season="第一季度";}
		if("04".equals(u_month)){u_season="第二季度";}
		if("05".equals(u_month)){u_season="第二季度";}
		if("06".equals(u_month)){u_season="第二季度";}
		if("07".equals(u_month)){u_season="第三季度";}
		if("08".equals(u_month)){u_season="第三季度";}
		if("09".equals(u_month)){u_season="第三季度";}
		if("10".equals(u_month)){u_season="第四季度";}
		if("11".equals(u_month)){u_season="第四季度";}
		if("12".equals(u_month)){u_season="第四季度";}

		StringBuffer buff = new StringBuffer();
		if(!exist_or_not(tesvr_time, tbe_id)) {	
			buff.append("insert into tes_vistor_record(tesvr_id,tbfi_id,tesvr_description,tesvr_vistor,tesvr_time,tesvr_memo,reserve1,reserve2,reserve3,reserve4,reserve5,create_by,create_time,last_modify_by,last_modify_time) ")
			.append(" values('").append(AccessDataBaseUtil.getPKUUID()).append("',(select t2.tbfi_id from tb_employee t1,tb_family_base t2 where t1.tbe_id=t2.tbe_id  and t1.tbe_id='")
			.append(tbe_id).append("'),'").append(tesvr_description).append("','").append(tesvr_vistor).append("','").append(tesvr_time).append("','").append(tesvr_memo)
			.append("','").append(u_season).append("',null,null,null,null,'").append(user.getUserId()).append("','").append(curTime).append("','").append(user.getUserId()).append("','").append(curTime).append("')"); 
		} else if(exist_or_not(tesvr_time, tbe_id)) {
			buff.append("update tes_vistor_record  set tesvr_description='").append(tesvr_description).append("',tesvr_vistor='").append(tesvr_vistor).append("',tesvr_time='").append(tesvr_time).append("',tesvr_memo='").append(tesvr_memo).append("',last_modify_by='").append(user.getUserId()).append("',last_modify_time='").append(curTime).append("' where year(tesvr_time) like '").append(a[0]).append("' and reserve1 like '").append(u_season).append("' and tbfi_id=(select tbfi_id from tb_family_base where tbe_id='").append(tbe_id).append("') ");
		}
		String sql=buff.toString();
		return sql;
	}
	
	
	

	//修改随访记录
	public Object xgfs (Object jsonArray) throws Exception {
		UserBean user = (UserBean)this.getRequest().getSession().getAttribute("userBean");
		String curTime = FormatUtil.getCurentDateTime();
		JSONArray array = JSONArray.fromObject(jsonArray);
		JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
		String tesvr_id = jsonObject.getString("tesvr_id");//记录ID
		String tesvr_description = jsonObject.getString("tesvr_description");//访视记录
		String tesvr_vistor = jsonObject.getString("tesvr_vistor");//访视人
		String tesvr_time = jsonObject.getString("tesvr_time");//访视时间
		String tesvr_memo = jsonObject.getString("tesvr_memo");//备注
		
		//判断第几季度
		String u_season="";
		String []a=tesvr_time.split("-");
		String u_month=a[1];
		if("01".equals(u_month)){u_season="第一季度";}
		if("02".equals(u_month)){u_season="第一季度";}
		if("03".equals(u_month)){u_season="第一季度";}
		if("04".equals(u_month)){u_season="第二季度";}
		if("05".equals(u_month)){u_season="第二季度";}
		if("06".equals(u_month)){u_season="第二季度";}
		if("07".equals(u_month)){u_season="第三季度";}
		if("08".equals(u_month)){u_season="第三季度";}
		if("09".equals(u_month)){u_season="第三季度";}
		if("10".equals(u_month)){u_season="第四季度";}
		if("11".equals(u_month)){u_season="第四季度";}
		if("12".equals(u_month)){u_season="第四季度";}

		String sql="update tes_vistor_record set tesvr_description='"+tesvr_description+"',tesvr_vistor='"+tesvr_vistor+"',tesvr_time='"+tesvr_time+"',tesvr_memo='"+tesvr_memo+"',last_modify_by='"+user.getUserId()+"',last_modify_time='"+curTime+"',reserve1='"+u_season+"' where tesvr_id='"+tesvr_id+"' ";
		String [] sqls={sql};
		AccessDataBaseUtil.executeSQLUpdate(sqls);
		return "\"修改随访记录成功\"";
	}
	
	//检查随访记录是否已经存在
	public boolean exist_or_not(String time,String uid) throws Exception
	{
		//判断第几季度
		String u_season="";
		String []a=time.split("-");
		String u_month=a[1];
		if("01".equals(u_month)){u_season="第一季度";}
		if("02".equals(u_month)){u_season="第一季度";}
		if("03".equals(u_month)){u_season="第一季度";}
		if("04".equals(u_month)){u_season="第二季度";}
		if("05".equals(u_month)){u_season="第二季度";}
		if("06".equals(u_month)){u_season="第二季度";}
		if("07".equals(u_month)){u_season="第三季度";}
		if("08".equals(u_month)){u_season="第三季度";}
		if("09".equals(u_month)){u_season="第三季度";}
		if("10".equals(u_month)){u_season="第四季度";}
		if("11".equals(u_month)){u_season="第四季度";}
		if("12".equals(u_month)){u_season="第四季度";}
		String exist_sql="select t3.tesvr_id from tb_employee t1 left join tb_family_base t2 on t1.tbe_id=t2.tbe_id left join tes_vistor_record t3 on t2.tbfi_id=t3.tbfi_id where t1.tbe_id='"+uid+"' and t3.reserve1='"+u_season+"' and year(t3.tesvr_time)='"+a[0]+"'";
		if(AccessDataBaseUtil.query4List(exist_sql, null).size() != 0) {
			return true;
		} // 存在
		else {
			return false;
		} //不存在
	}
	
	//获取detailPanel中的值
	public Object load_detailPanel(Object jsonArray) throws Exception {
		String[] fields_detailPanel = {"tesvr_id","tbfi_id","tesvr_description","tesvr_vistor","tesvr_time","tesvr_memo","reserve1","reserve2","reserve3","reserve4","reserve5","create_by","create_time","last_modify_by","last_modify_time"};
 		Object[] params = null;
		JSONArray array = JSONArray.fromObject(jsonArray);
 		JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
 		String tbe_id = jsonObject.getString("tbe_id");//主键ID
		String year = jsonObject.getString("year");//获取年份
		String sql="from tb_employee t1,tb_family_base t2,tes_vistor_record t3 where t1.tbe_id=t2.tbe_id and t2.tbfi_id=t3.tbfi_id and t1.tbe_id='"+tbe_id+"' ";
		if("null".equals(year)) {
			sql += " ";
		} else {
			sql += " and year(tesvr_time) like '%" + year + "%' order by t3.tesvr_time asc ";
		}	
		String countSql = "select count(*) " + sql;
		String querySql = "select t3.tesvr_id,t3.tbfi_id,t3.tesvr_description,t3.tesvr_vistor,t3.tesvr_time,t3.tesvr_memo,t3.reserve1,t3.reserve2,t3.reserve3,t3.reserve4,t3.reserve5,t3.create_by,t3.create_time,t3.last_modify_by,t3.last_modify_time "+sql;				
		return AccessDataBaseUtil.query4SQLPagging(fields_detailPanel, countSql, querySql, this.getPage(), this.getPagesize(), params);
	}
	
	
	@Override
	public Object update(Object jsonArray) throws Exception {
		return null;
	}
	
	//判断某月是第几季度
	public static String season_from_month(String month)
	{
		String jidu="";
		if("01".equals(month)){jidu="1";}
		if("02".equals(month)){jidu="1";}
		if("03".equals(month)){jidu="1";}
		if("04".equals(month)){jidu="2";}
		if("05".equals(month)){jidu="2";}
		if("06".equals(month)){jidu="2";}
		if("07".equals(month)){jidu="3";}
		if("08".equals(month)){jidu="3";}
		if("09".equals(month)){jidu="3";}
		if("10".equals(month)){jidu="4";}
		if("11".equals(month)){jidu="4";}
		if("12".equals(month)){jidu="4";}
		return jidu;
	}
	
	//根据季度得到该季度首月跟末月
	public static int yue_from_season(int season,int flag)
	{
		int a=0;
		if(flag==0)
		{if(season==1){a=1;}
		 if(season==2){a=4;}
		 if(season==3){a=7;}
		 if(season==4){a=10;}
		}
		if(flag==1)
		{if(season==1){a=3;}
		 if(season==2){a=6;}
		 if(season==3){a=9;}
		 if(season==4){a=12;}
		}
		return a;
	}
	
	// 判断是否逾期
	public boolean yuqi(Object jsonArray) throws Exception {
		String curTime = FormatUtil.getCurentDate();  //yyyy-mm-dd
		JSONArray array = JSONArray.fromObject(jsonArray);
		JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
		String tbe_id = jsonObject.getString("tbe_id");//获取职员ID
		String jidu="";//现在的季度
		String []a=curTime.split("-");
		String u_month=a[1];
		jidu=season_from_month(u_month);
		String sql_find_mmdd=" select SC_VALUE from t_system_config  where SC_UID = '0' and SC_KEY ='"+jidu+"' ";
		String data_role=AccessDataBaseUtil.query4List(sql_find_mmdd,null).toString().replace("[", "").replace("]", "");
		String u_year=a[0];
		String yyyy_mm_dd = "";
		yyyy_mm_dd = u_year + "-" + data_role; // 根据当前季度得到的逾期提醒时间，yyyy-mm-dd

		String sql_exite = "";
		int row_val=0;
		
		String sql_select_keytime="select tbe_es_time from tb_employee where tbe_id='"+tbe_id+"' ";
		String keytime=AccessDataBaseUtil.query4List(sql_select_keytime, null).toString().replace("[", "").replace("]", "");//设为重点时间
		if("".equals(keytime)||" ".equals(keytime)||keytime==null){return true;} //判断设为重点时间是否存在
		String []keytime_arry=keytime.split("-"); 
		String sql_role_time_from_keytime="select SC_VALUE from t_system_config  where SC_UID = '0' and SC_KEY ='"+season_from_month(keytime_arry[1])+"' ";
		String time_role_from_keytime=AccessDataBaseUtil.query4List(sql_role_time_from_keytime,null).toString().replace("[", "").replace("]", "");
		String keytime_yyyy_mm_dd=u_year + "-" + time_role_from_keytime; //根据设为重点对象时间 得到的逾期提醒时间，yyyy-mm-dd

		//System.out.println("设为重点时间："+keytime);
		//System.out.println("重点时间 规则："+keytime_yyyy_mm_dd);
		
		if(Integer.valueOf(keytime_arry[0]).intValue()== Integer.valueOf(u_year).intValue())
		{
			if(keytime.compareTo(keytime_yyyy_mm_dd)<0)
			{
				if(curTime.compareTo(yyyy_mm_dd) < 0)
				{
					int season_from=(Integer.valueOf(season_from_month(keytime_arry[1])).intValue());
					int season_to=(Integer.valueOf(season_from_month(u_month)).intValue()-2);
					sql_exite="select count(*) from tb_employee t1 left join tb_family_base t2 on t1.tbe_id=t2.tbe_id left join tes_vistor_record t3 on t3.tbfi_id=t2.tbfi_id where t1.tbe_id='"+tbe_id+"' and t3.tesvr_time like '"+( Integer.valueOf(u_year).intValue())+"%' and  month(t3.tesvr_time) >= '"+yue_from_season(season_from,0)+"' and month(t3.tesvr_time) <= '"+yue_from_season(season_to,1)+"' "; 
					row_val=season_to-season_from+1;
				}
				else if(curTime.compareTo(yyyy_mm_dd) >= 0)
				{
					int season_from=(Integer.valueOf(season_from_month(keytime_arry[1])).intValue());
					int season_to=(Integer.valueOf(season_from_month(u_month)).intValue()-1);
					sql_exite="select count(*) from tb_employee t1 left join tb_family_base t2 on t1.tbe_id=t2.tbe_id left join tes_vistor_record t3 on t3.tbfi_id=t2.tbfi_id where t1.tbe_id='"+tbe_id+"' and t3.tesvr_time like '"+( Integer.valueOf(u_year).intValue())+"%' and  month(t3.tesvr_time) >= '"+yue_from_season(season_from,0)+"' and month(t3.tesvr_time) <= '"+yue_from_season(season_to,1)+"' "; 
					row_val=season_to-season_from+1;
				}
				
			}
			else if(keytime.compareTo(keytime_yyyy_mm_dd)>=0)
			{
				if(curTime.compareTo(yyyy_mm_dd) < 0)
				{
					int season_from=(Integer.valueOf(season_from_month(keytime_arry[1])).intValue());
					int season_to=(Integer.valueOf(season_from_month(u_month)).intValue()-2);
					sql_exite="select count(*) from tb_employee t1 left join tb_family_base t2 on t1.tbe_id=t2.tbe_id left join tes_vistor_record t3 on t3.tbfi_id=t2.tbfi_id where t1.tbe_id='"+tbe_id+"' and t3.tesvr_time like '"+( Integer.valueOf(u_year).intValue())+"%' and  month(t3.tesvr_time) >= '"+yue_from_season(season_from,0)+"' and month(t3.tesvr_time) <= '"+yue_from_season(season_to,1)+"' "; 
					row_val=season_to-season_from+1;
				}
				else if(curTime.compareTo(yyyy_mm_dd) >= 0)
				{
					int season_from=(Integer.valueOf(season_from_month(keytime_arry[1])).intValue());
					int season_to=(Integer.valueOf(season_from_month(u_month)).intValue()-1);
					sql_exite="select count(*) from tb_employee t1 left join tb_family_base t2 on t1.tbe_id=t2.tbe_id left join tes_vistor_record t3 on t3.tbfi_id=t2.tbfi_id where t1.tbe_id='"+tbe_id+"' and t3.tesvr_time like '"+( Integer.valueOf(u_year).intValue())+"%' and  month(t3.tesvr_time) >= '"+yue_from_season(season_from,0)+"' and month(t3.tesvr_time) <= '"+yue_from_season(season_to,1)+"' "; 
					row_val=season_to-season_from+1;
				}
			}
		}
		else if(Integer.valueOf(keytime_arry[0]).intValue()< Integer.valueOf(u_year).intValue())
		{
			if(curTime.compareTo(yyyy_mm_dd) < 0) {
				if("1".equals(jidu))  
				{sql_exite="select count(*) from tb_employee t1 left join tb_family_base t2 on t1.tbe_id=t2.tbe_id left join tes_vistor_record t3 on t3.tbfi_id=t2.tbfi_id where t1.tbe_id='"+tbe_id+"' and t3.tesvr_time like '"+( Integer.valueOf(u_year).intValue()-1)+"%' and  t3.reserve1 in ('第一季度','第二季度','第三季度') ";
				row_val=3;}
				if("2".equals(jidu))  
				{sql_exite="select count(*) from tb_employee t1 left join tb_family_base t2 on t1.tbe_id=t2.tbe_id left join tes_vistor_record t3 on t3.tbfi_id=t2.tbfi_id where t1.tbe_id='"+tbe_id+"' and t3.tesvr_time like '"+( Integer.valueOf(u_year).intValue()-1)+"%' and  t3.reserve1 in ('第一季度','第二季度','第三季度','第四季度') ";
				row_val=4;}
				if("3".equals(jidu))
				{sql_exite="select count(*) from tb_employee t1 left join tb_family_base t2 on t1.tbe_id=t2.tbe_id left join tes_vistor_record t3 on t3.tbfi_id=t2.tbfi_id where t1.tbe_id='"+tbe_id+"' and t3.tesvr_time like '"+u_year+"%' and  t3.reserve1 in ('第一季度') ";
				row_val=1;}
				if("4".equals(jidu))
				{sql_exite="select count(*) from tb_employee t1 left join tb_family_base t2 on t1.tbe_id=t2.tbe_id left join tes_vistor_record t3 on t3.tbfi_id=t2.tbfi_id where t1.tbe_id='"+tbe_id+"' and t3.tesvr_time like '"+u_year+"%' and  t3.reserve1 in ('第一季度','第二季度') ";
				row_val=2;}
			}
			if(curTime.compareTo(yyyy_mm_dd) >= 0) {
				if("1".equals(jidu))
				{sql_exite="select count(*) from tb_employee t1 left join tb_family_base t2 on t1.tbe_id=t2.tbe_id left join tes_vistor_record t3 on t3.tbfi_id=t2.tbfi_id where t1.tbe_id='"+tbe_id+"' and t3.tesvr_time like '"+( Integer.valueOf(u_year).intValue()-1)+"%' and  t3.reserve1 in ('第一季度','第二季度','第三季度','第四季度') "; row_val=4;}
				if("2".equals(jidu))
				{sql_exite="select count(*) from tb_employee t1 left join tb_family_base t2 on t1.tbe_id=t2.tbe_id left join tes_vistor_record t3 on t3.tbfi_id=t2.tbfi_id where t1.tbe_id='"+tbe_id+"' and t3.tesvr_time like '"+u_year+"%' and  t3.reserve1 in ('第一季度')  "; row_val=1;}
				if("3".equals(jidu))
				{sql_exite="select count(*) from tb_employee t1 left join tb_family_base t2 on t1.tbe_id=t2.tbe_id left join tes_vistor_record t3 on t3.tbfi_id=t2.tbfi_id where t1.tbe_id='"+tbe_id+"' and t3.tesvr_time like '"+u_year+"%' and  t3.reserve1 in ('第一季度','第二季度') ";row_val=2;}
				if("4".equals(jidu))
				{sql_exite="select count(*) from tb_employee t1 left join tb_family_base t2 on t1.tbe_id=t2.tbe_id left join tes_vistor_record t3 on t3.tbfi_id=t2.tbfi_id where t1.tbe_id='"+tbe_id+"' and t3.tesvr_time like '"+u_year+"%' and  t3.reserve1 in ('第一季度','第二季度','第三季度') ";row_val=3;}
			}
		}
		else if(Integer.valueOf(keytime_arry[0]).intValue()> Integer.valueOf(u_year).intValue())
		{
		   return true;
		}
		
		//System.out.println(row_val);
		//System.out.println(sql_exite);
		
		int value = Integer.parseInt(AccessDataBaseUtil.query4List(sql_exite, null).toString().replace("[", "").replace("]", ""));
		if(value < row_val) { // 逾期
			return false;
		} else {   //不逾期
			return true;
		}
	}
	
	public Object load_and_set(Object jsonArray) throws Exception {
		String curTime = FormatUtil.getCurentDate();  //yyyy-mm-dd
		JSONArray array = JSONArray.fromObject(jsonArray);
		JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
		String tbe_id = jsonObject.getString("tbe_id");
		String season = jsonObject.getString("season");	
		String []a=curTime.split("-");
		String sql="select t3.tesvr_description,t3.tesvr_vistor,t3.tesvr_time,t3.tesvr_memo,t3.reserve1 from tb_employee t1 left join tb_family_base t2 on t1.tbe_id=t2.tbe_id left join tes_vistor_record t3 on t2.tbfi_id=t3.tbfi_id where t1.tbe_id='"+tbe_id+"' and year(t3.tesvr_time) like '"+a[0]+"' and t3.reserve1='"+season+"' ";
		String [] fields_select={"tesvr_description","tesvr_vistor","tesvr_time","tesvr_memo","reserve1"};
		JSONArray temp = JSONArray.fromObject(AccessDataBaseUtil.query(fields_select, sql, null ));
		return temp.toString().substring(1, temp.toString().length()-1);	
	}
	
	@Override
	public Object delete(Object jsonArray) throws Exception {
		return null;
	}

	@Override
	public Object recordCount(Object jsonArray) throws Exception {
		return null;
	}

	@Override
	public Object setRequest(HttpServletRequest request) throws Exception {
		this.request = request;
		return request;
	}

	@Override
	public Object updateBatch(Object jsonArray) throws Exception {
		return null;
	}

	@Override
	public Object onCreate(Object jsonArray) {
		return null;
	}

	@Override
	public Object onDestory(Object jsonArray) {
		return null;
	}

	@Override
	public Object onInit(Object jsonArray) {
		return null;
	}
	
	/** 
	 * 描述：重点对象导出
	 */
	public Object expData(Object jsonArray) throws Exception {
		UserBean user = (UserBean)this.getRequest().getSession().getAttribute("userBean");
		JSONArray array = JSONArray.fromObject(jsonArray);
		JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
		String orgIds = jsonObject.getString("orgIds");//机构ID串
		String tbe_name = jsonObject.getString("tbe_name");//姓名
		String tbe_idcard = jsonObject.getString("tbe_idcard");//身份证

		StringBuffer buff = new StringBuffer();
		buff.append("select t1.reserve1,t1.tbe_name,t1.tbe_idcard,MAX(t3.tesvr_time),t1.tbe_sex,");
		if("mysql".equals(user.getDbType())){
			buff.append(" case when t2.tbfi_children_num='0' then '无' when t2.tbfi_children_num='1' then '1' when t2.tbfi_children_num='2' then '2' when t2.tbfi_children_num='3' then '多个' END ,") ;
		}else if("oracle".equals(user.getDbType())) {
			buff.append(" decode(t2.tbfi_children_num='0','无','有'),") ; 
		}
		buff.append(" (select tmgd_name from TM_GENERAL_DICTIONARY where tmgd_id = t1.tbe_marital_status),")
		.append(" t2.tbfi_spouse_name,t2.tbfi_spouse_sex,t2.tbfi_spouse_department,")
		.append("(select tmgd_name from TM_GENERAL_DICTIONARY where tmgd_id = tbe_birth_control_measures),")
		.append("(select tmgd_name from TM_GENERAL_DICTIONARY where tmgd_id = tbe_es_reason),")
		.append(" t1.tbe_address, ")
		
		.append(" (select a1.tesvr_description from tes_vistor_record a1 where a1.tbfi_id=t2.tbfi_id and a1.reserve1='第一季度' and year(a1.tesvr_time)=year(CURRENT_DATE) ), ")
		.append(" (select a1.tesvr_vistor from tes_vistor_record a1 where a1.tbfi_id=t2.tbfi_id and a1.reserve1='第一季度' and year(a1.tesvr_time)=year(CURRENT_DATE) ), ")
		.append(" (select a1.tesvr_time from tes_vistor_record a1 where a1.tbfi_id=t2.tbfi_id and a1.reserve1='第一季度' and year(a1.tesvr_time)=year(CURRENT_DATE) ), ")
		.append(" (select a1.tesvr_memo from tes_vistor_record a1 where a1.tbfi_id=t2.tbfi_id and a1.reserve1='第一季度' and year(a1.tesvr_time)=year(CURRENT_DATE) ), ")
		
        .append(" (select tesvr_description from tes_vistor_record where tbfi_id=t2.tbfi_id and reserve1='第二季度' and year(tesvr_time)=year(CURRENT_DATE) ), ")
		.append(" (select tesvr_vistor from tes_vistor_record where tbfi_id=t2.tbfi_id and reserve1='第二季度' and year(tesvr_time)=year(CURRENT_DATE) ), ")
		.append(" (select tesvr_time from tes_vistor_record where tbfi_id=t2.tbfi_id and reserve1='第二季度' and year(tesvr_time)=year(CURRENT_DATE) ), ")
		.append(" (select tesvr_memo from tes_vistor_record where tbfi_id=t2.tbfi_id and reserve1='第二季度' and year(tesvr_time)=year(CURRENT_DATE) ), ")
		
		.append(" (select tesvr_description from tes_vistor_record where tbfi_id=t2.tbfi_id and reserve1='第三季度' and year(tesvr_time)=year(CURRENT_DATE) ), ")
		.append(" (select tesvr_vistor from tes_vistor_record where tbfi_id=t2.tbfi_id and reserve1='第三季度' and year(tesvr_time)=year(CURRENT_DATE) ), ")
		.append(" (select tesvr_time from tes_vistor_record where tbfi_id=t2.tbfi_id and reserve1='第三季度' and year(tesvr_time)=year(CURRENT_DATE) ), ")
		.append(" (select tesvr_memo from tes_vistor_record where tbfi_id=t2.tbfi_id and reserve1='第三季度' and year(tesvr_time)=year(CURRENT_DATE) ), ")
		
		.append(" (select tesvr_description from tes_vistor_record where tbfi_id=t2.tbfi_id and reserve1='第四季度' and year(tesvr_time)=year(CURRENT_DATE) ), ")
		.append(" (select tesvr_vistor from tes_vistor_record where tbfi_id=t2.tbfi_id and reserve1='第四季度' and year(tesvr_time)=year(CURRENT_DATE) ), ")
		.append(" (select tesvr_time from tes_vistor_record where tbfi_id=t2.tbfi_id and reserve1='第四季度' and year(tesvr_time)=year(CURRENT_DATE) ), ")
		.append(" (select tesvr_memo from tes_vistor_record where tbfi_id=t2.tbfi_id and reserve1='第四季度' and year(tesvr_time)=year(CURRENT_DATE) ) ")
		
		.append(" from tb_employee t1 left join tb_family_base t2 on t1.tbe_id=t2.tbe_id left join tes_vistor_record t3 on t3.tbfi_id=t2.tbfi_id  where t1.tbe_is_es='1' and t1.tbe_employment_forms <> '31' and t1.tbe_status not in ('102-3','102-4','102-5') ");	
		if(StringUtils.isBlank(orgIds)){
			buff.append(" and t1.org_id in (").append(user.getCurSubOrgId()).append(")");
		} else {
			buff.append(" and t1.org_id in (").append(user.getCurSubOrgIds(orgIds)).append(")");
		}
		if(StringUtils.isNotBlank(tbe_name)) {
			buff.append(" and t1.tbe_name like '%").append(tbe_name).append("%'");
		}
		if(StringUtils.isNotBlank(tbe_idcard)) {
			buff.append(" and t1.tbe_idcard like '%").append(tbe_idcard).append("%'");
		}
		buff.append(" group by t1.tbe_id order by t3.tesvr_time asc ");
		List<Object[]> list = AccessDataBaseUtil.query(buff.toString(), null);
		String url = this.getRequest().getSession().getServletContext().getRealPath("/jsp/app/xls/");
		String model = url + "/model/keyReg.xls";
		String file = url + "/file/keyReg.xls";
		String rfile = "jsp/app/xls/file/keyReg.xls";
		File ffrom = new File(model);
		File fto = new File(file);
		Common.copyFile(ffrom, fto);
		//if(list.size() > 0){
			XLSUtils.writeXls(list, fto, "重点服务对象登记表", 3);//写xls内容
		//} else {
		//	return "{\"result\":false,\"resultmessage\":\"\",\"resultrecord\":\"导出结果集没有记录\"}";
		//}
		return "\"" + rfile + "\"";
	}
	
}


